package leave.dataaccess.mysql;

import java.sql.*;

import leave.dataaccess.*;

import leave.model.CompensationLeaveLedger;
import leave.model.Employee;
import leave.model.impl.mysql.CompensationLeaveLedgerImpl;

import java.util.ArrayList;


import leave.status.CompensationClaimStatus;


public class CompensationLeaveLedgerDAOImpl
		implements
			CompensationLeaveLedgerDAO {

	private Connection FactoryConnection;
	
	public CompensationLeaveLedgerDAOImpl(Connection factoryConnection) {
		super();
		FactoryConnection = factoryConnection;
	}

	@Override
	public CompensationLeaveLedger getNewLedger() {
		return new CompensationLeaveLedgerImpl();
	}

	/*
	 * (non-Javadoc)
	 * @see leave.dataaccess.CompensationLeaveLedgerDAO#getAllLedger(leave.model.Employee)
	 * Gets all ledgers applied by the employee.
	 */
	@Override
	public CompensationLeaveLedger[] getAllLedger(Employee employee) {
		ArrayList<CompensationLeaveLedger> arryList = new ArrayList<CompensationLeaveLedger>();
		String selectSql = "SELECT * FROM compensation_leave_ledger";
		return retrieveData(arryList, selectSql);
	}

	/*
	 * (non-Javadoc)
	 * @see leave.dataaccess.CompensationLeaveLedgerDAO#getPendingLedger(leave.model.Employee)
	 * Gets all ledgers pending for approval (with status "Qualified" and "Not Qualified")
	 */
	@Override
	public CompensationLeaveLedger[] getPendingLedger(Employee employee) {
		ArrayList<CompensationLeaveLedger> arryList = new ArrayList<CompensationLeaveLedger>();
		String selectPending = "SELECT * FROM compensation_leave_ledger where status='"
				+ CompensationClaimStatus.QUALIFIED.getStatusDescription()
				+ "' OR "
				+ "status='"
				+ CompensationClaimStatus.NOT_QUALIFIED.getStatusDescription() + "'";
		return retrieveData(arryList, selectPending);
	}
	
	/*
	 * (non-Javadoc)
	 * @see leave.dataaccess.CompensationLeaveLedgerDAO#addLedger(leave.model.CompensationLeaveLedger)
	 * Adds ledger to the database.
	 */
	@Override
	public void addLedger(CompensationLeaveLedger ledger) {
		String InsertSql = "insert into compensation_leave_ledger"
			+ "(employee_id,sign_in_time,sign_out_time,date_of_overtime,status,remarks,approved_by) values("
			+ ledger.getEmployee().getEmployeeID() + ",'"
			+ ledger.getStartTime() + "','" + ledger.getEndTime() + "','"
			+ ledger.getDateOfOvertime() + "','" + ledger.getStatus()
			+ "','" + ledger.getRemarks() + "',"
			+ ledger.getApprovedBy().getEmployeeID() + ")";
		int updated = 0;
		Statement st = null;
		try {
			st = FactoryConnection.createStatement();
			updated = st.executeUpdate(InsertSql);
			if (updated > 0) {
				System.out
				.println("Insert CompensationLeaveLedger successfully, record="
						+ updated);
			} else {
				System.out
				.println("Error in inserting CompensationLeaveLedger");
			}
		} catch (Exception e) {
			System.out.println("Caught exception in AddLedger");
		}
	}

	/*
	 * (non-Javadoc)
	 * @see leave.dataaccess.CompensationLeaveLedgerDAO#updateLedger(leave.model.CompensationLeaveLedger)
	 * Updates ledger to the database.
	 */
	@Override
	public void updateLedger(CompensationLeaveLedger ledger) {
		Statement st = null;
		String updateSelect = "update compensation_leave_ledger set employee_id="
				+ ledger.getEmployee().getEmployeeID()
				+ ",sign_in_time='"
				+ ledger.getStartTime()
				+ "',sign_out_time='"
				+ ledger.getEndTime()
				+ "',date_of_overtime='"
				+ ledger.getDateOfOvertime().toString()
				+ "',status='"
				+ ledger.getStatus()
				+ "',remarks='"
				+ ledger.getRemarks()
				+ "',approved_by="
				+ ledger.getApprovedBy().getEmployeeID()
				+ " where transaction_id=" + ledger.getTransactionID();
		try {
			st = FactoryConnection.createStatement();
			int updated = 0;
			updated = st.executeUpdate(updateSelect);
			if (updated > 0) {
				System.out.println("update successfully!");
			} else {
				System.out.println("update fail!");
			}
		} catch (Exception e) {

		}
	}

	/*
	 * (non-Javadoc)
	 * @see leave.dataaccess.CompensationLeaveLedgerDAO#deleteLedger(leave.model.CompensationLeaveLedger)
	 * Removes ledger from the database.
	 */
	@Override
	public void deleteLedger(CompensationLeaveLedger ledger) {
		String deletSql = "delete from compensation_leave_ledger where transaction_id="
			+ ledger.getTransactionID();
		try {
			Statement st = FactoryConnection.createStatement();
			int updated = 0;
			updated=st.executeUpdate(deletSql);
			if(updated>0){
				System.out.println("successful deleted");
			}
			else
			{
				System.out.println("deletion fail");
			}

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	
	
	/*
	 * 	Private function
	 */
	private CompensationLeaveLedger[] retrieveData(
			ArrayList<CompensationLeaveLedger> arryList, String selectSql) {
		Statement st = null;
		try {
			st = FactoryConnection.createStatement();
			System.out.println("select statement :" + selectSql);
			ResultSet rs = st.executeQuery(selectSql);
			while (rs.next()) {
				Employee supervisor = new EmployeeLeaveDAOImpl(
						FactoryConnection)
						.getEmployee(rs.getInt("employee_id"));
				CompensationLeaveLedger ledger = new CompensationLeaveLedgerImpl();
				ledger.setTransactionID(rs.getInt("transaction_id"));
				ledger.setApprovedBy(supervisor);
				ledger.setDateOfOvertime(rs.getDate("date_of_overtime"));
				ledger.setEndTime(rs.getTime("sign_out_time"));
				ledger.setStartTime(rs.getTime("sign_in_time"));
				ledger.setStatus(CompensationClaimStatus.valueOf(rs
						.getString("status")));
				ledger.setRemarks(rs.getString("remarks"));
				arryList.add(ledger);
			}
			st.close();
		} catch (Exception e) {
			System.out.println("Error in select the Compensation Leave ledger");

		}

		return (CompensationLeaveLedger[]) arryList.toArray();
	}

}
